BEGIN;

alter table win_common_names add column gender varchar;
alter table win_common_names add column lifestage varchar;

update win_common_names set gender = wo.gender, lifestage = wo.lifestage from win_observations wo where win_common_names.observation_id = wo.id;

alter table win_observations drop column gender cascade;
alter table win_observations drop column lifestage cascade;

CREATE OR REPLACE VIEW win_observation_textual_data_view AS
 SELECT win_observations.id, win_observations.s_kingdom, win_observations.s_phylum, win_observations.s_class, win_observations.s_order, win_observations.s_family, win_observations.s_genus, win_observations.s_species, win_observations.s_scientific_name, win_observations.waterbody_type, win_observations.waterbody_name, win_observations.comments, win_observations.record_language, win_observer.fname, win_observer.lname, win_observer.affiliation, win_observer.address, win_observer.email, win_observation_location.country, win_observation_location.region, win_observation_location.district, win_observation_location.city, win_observation_location.locality
   FROM win_observations, win_observer, win_observation_location
  WHERE win_observations.observer_id = win_observer.id AND win_observations.id = win_observation_location.observation_id
  ORDER BY win_observations.id;

COMMIT;